###############################################
#                                             #
#     Merge in new RCVs                       #
#     Created by Mac Lockhart Mar 3 2023      #
#     Updated Mar 03 2023                     #
#                                             #
###############################################

library(tidyverse)
library(readr)
library(fuzzyjoin)
library(car)
library(stringr)
library(maps)
library(data.table)
library(readxl)
setwd(rprojroot::find_root(rprojroot::is_rstudio_project))

old <- read.csv("Intermediate Data/Congressional Roll Call Votes/final_roll_call_votes_agustin.csv")
new <- read_xls("Intermediate Data/Congressional Roll Call Votes/AdditionalVotes_V2.xls")
new_2 <- read_xls("Intermediate Data/Congressional Roll Call Votes/hs_votes_06-08_21-22.xls")
new_3 <- read_xlsx("Intermediate Data/Congressional Roll Call Votes/hs_votes_10.xlsx")

colnames(new) <- c("icpsr", "id", "name", "state_abbrev",
                   paste0("V", 65:(length(colnames(new))-4+65)))


df<-full_join(old, new, by=c("icpsr", "name", "state_abbrev", "id"))

colnames(new_2)<- c("icpsr", "id", "name", "state_abbrev",
                    paste0("V", 75:(length(colnames(new_2))-4+75)))

df<-full_join(df, new_2, by=c("icpsr", "name", "state_abbrev", "id"))

colnames(new_3)<- c("icpsr", "id", "name", "state_abbrev",
                    paste0("V", 109:111))

df<-full_join(df, new_3, by=c("icpsr", "name", "state_abbrev", "id"))


write.csv(df, "Intermediate Data/roll_call_votes_for_merge.csv")
